package com.hdl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.hdl.commons.DBUtil;
import com.hdl.dao.UserDao;
import com.hdl.dto.UserSearch;
import com.hdl.entity.HdlUser;
import com.hdl.util.Druidutils;
import com.hdl.util.JdbcUtil;
import com.hdl.vo.HdlUserVO;

import cn.hutool.core.util.StrUtil;

public class UserDaoImpl implements UserDao{

	/**
	 * 工具对象
	 */
	private JdbcTemplate template = new JdbcTemplate(Druidutils.getDataSource());
	
	@Override
	public HdlUser login(HdlUser loginUser) {
		
		//定义sql语句
		String sql = "select * from hdl_user where HDL_USER_NAME = ? and HDL_USER_PSD = ?";
		HdlUser user = null;
		try {
			user = template.queryForObject(sql, new BeanPropertyRowMapper<HdlUser>(HdlUser.class),loginUser.getHdlUserName(),loginUser.getHdlUserPsd());
		}catch (Exception e) {
			
		}
		return user;
		
	}

	@Override
	public void register(HdlUser user) {
		
		//定义sql语句
		String sql = "insert into hdl_user(HDL_USER_ID,SYS_ROLE_ID,HDL_USER_NAME,HDL_USER_PSD,HDL_USER_REALNAME,HDL_USER_EMAIL,HDL_USER_SEX,HDL_USER_BIRTHDAY,HDL_USER_PHONE,HDL_USER_SALT,HDL_USER_STATUS,HDL_USER_ACTIVECODE,HDL_USER_SCORE,HDL_USER_ZFB,HDL_USER_WX,HDL_USER_LOG) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
		template.update(sql,user.getHdlUserId(),user.getSysRoleId(),user.getHdlUserName(),user.getHdlUserPsd(),
							user.getHdlUserRealname(),user.getHdlUserEmail(),user.getHdlUserSex(),user.getHdlUserBirthday(),
							user.getHdlUserPhone(),user.getHdlUserSalt(),user.getHdlUserStatus(),user.getHdlUserActiveCode(),
							user.getHdlUserScore(),user.getHdlUserZFB(),user.getHdlUserWX(),user.getHdlUserLog()
				);
	}

	@Override
	public int active(String code) {
		
		//定义sql语句
		String sql = "update hdl_user set HDL_USER_STATUS = 0 where HDL_USER_ACTIVECODE = ? ";
		return template.update(sql,code);
	}

	/**
	 * 统计user表的记录总数
	 * @return
	 * @throws SQLException
	 */
	public long count() throws SQLException {
		String sql = "select count(*) as c from hdl_user";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			return rs.getLong(1);
		} finally {
			JdbcUtil.close(rs, pstmt,conn);
		}
	}

	@Override

	public void update(HdlUser user) {
		
		//定义sql语句
		String sql = "update HDL_USER set SYS_ROLE_ID = ?, HDL_USER_NAME = ?, HDL_USER_PSD = ?, HDL_USER_REALNAME = ?, HDL_USER_EMAIL = ?, HDL_USER_SEX = ?, HDL_USER_BIRTHDAY = ?, HDL_USER_PHONE = ?, HDL_USER_SALT = ?, HDL_USER_STATUS = ?, HDL_USER_ACTIVECODE = ?, HDL_USER_SCORE = ?, HDL_USER_ZFB = ?, HDL_USER_WX = ?, HDL_USER_LOG = ? WHERE HDL_USER_ID = ?";
		template.update(sql,user.getSysRoleId(),user.getHdlUserName(),user.getHdlUserPsd(),user.getHdlUserRealname(),
				user.getHdlUserEmail(),user.getHdlUserSex(),user.getHdlUserBirthday(),user.getHdlUserPhone(),
				user.getHdlUserSalt(),user.getHdlUserStatus(),user.getHdlUserActiveCode(),user.getHdlUserScore(),
				user.getHdlUserZFB(),user.getHdlUserWX(),user.getHdlUserLog(),user.getHdlUserId());
	}

	@Override
	public HdlUser selectById(String userId) {
		
		//定义sql语句
		String sql = "select * from HDL_USER WHERE HDL_USER_ID = ?";
		return template.queryForObject(sql, new BeanPropertyRowMapper<HdlUser>(HdlUser.class),userId);
	}
	
	

	public HdlUser selectUserById(String userId) {
		
		//定义sql语句
		String sql = "select * from HDL_USER WHERE HDL_USER_ID = ?";
		
		return template.queryForObject(sql, new BeanPropertyRowMapper<HdlUser>(HdlUser.class),userId);
	}


	@Override
	public void UpdatePsd(String id, String psd) {
          String sql = "UPDATE hdl_user SET HDL_USER_PSD=? WHERE HDL_USER_ID=?";
          template.update(sql,psd,id);
	}

	@Override
	public HdlUser findPsdByEmail(String email) {
		
		//定义sql语句
		String sql = "select * from HDL_USER WHERE HDL_USER_EMAIL = ? ";
		
		return template.queryForObject(sql, new BeanPropertyRowMapper<HdlUser>(HdlUser.class),email);

	}

	@Override
	public boolean checkName(String userName) {
		
		//定义sql语句
		String sql = "select * from HDL_USER WHERE HDL_USER_NAME = ?";
		HdlUser user = null;
		try {
			user = template.queryForObject(sql, new BeanPropertyRowMapper<HdlUser>(HdlUser.class),userName);
		}catch(Exception e) {

		}
		if(user != null) {
			return false;
		}
		return true;
	}

	@Override
	public long count(UserSearch search) throws SQLException {
		StringBuffer sql=new StringBuffer("select count(*) as c FROM hdl_user a INNER JOIN sys_role b ON a.SYS_ROLE_ID = b.SYS_ROLE_ID");
		List<Object> pstm=new ArrayList<Object>();
			return DBUtil.getLong(sql.toString(),pstm.toArray() );
	}

	@Override
	public List<HdlUserVO> select(UserSearch user, int index, int len) throws SQLException {
		StringBuffer sql=new StringBuffer("SELECT a.*,b.SYS_ROLE_NAME FROM hdl_user a INNER JOIN sys_role b ON a.SYS_ROLE_ID = b.SYS_ROLE_ID");
		List<Object> pstm=new ArrayList<Object>();
		where(user,sql,pstm);
		sql.append(" LIMIT ?,? ");
		pstm.add(index);
		pstm.add(len);
        return DBUtil.list(sql.toString(), HdlUserVO.class, pstm.toArray());
	}
	private void where(UserSearch search,StringBuffer sql,List<Object> params)throws SQLException {
		if(search.getHdlUserid()!=null){
			sql.append(" AND b.SYS_ROLE_ID=?");
			params.add(search.getHdlUserid());
		}
		if(!StrUtil.hasBlank(search.getKey())){
			sql.append(" and  (a.SYS_ROLE_ID=? or a.HDL_USER_NAME  like CONCAT('%',?,'%'))");
			params.add(search.getHdlUserid());
			params.add(search.getKey());
		}
	}

	@Override
	public HdlUser selectModel(HdlUser user) throws SQLException {
		StringBuffer sql=new StringBuffer ("SELECT * FROM hdl_user where 1=1 ");
		List<Object> pstm=new ArrayList<Object>();
		where(sql,user,pstm);
		return DBUtil.get(sql.toString(),HdlUser.class , pstm.toArray());
	}
	
	@Override
	public List<HdlUser> selectModel(HdlUser user, int index, int len) throws SQLException {
		StringBuffer sql=new StringBuffer("SELECT * FROM hdl_user where 1=1");
		List<Object> pstm=new ArrayList<Object>();
		where(sql,user,pstm);
		sql.append("  LIMIT  ?,? ");
		pstm.add( index);
		pstm.add( len);
		return DBUtil.list(sql.toString(),HdlUser.class ,pstm.toArray() );		
	}
	//查询非空字段
	@Override
	public long selectModelCount(HdlUser user) throws SQLException {
		StringBuffer sql=new StringBuffer("SELECT COUNT(*) as c FROM hdl_user where 1=1");
		List<Object> pstm=new ArrayList<Object>();
		where(sql,user,pstm);
		return DBUtil.getLong(sql.toString(), HdlUser.class,pstm.toArray());
	}
	private void where(StringBuffer sql, HdlUser user, List<Object> pstm) {
		if(user==null) {
			return ;
		}
		if(user.getHdlUserActiveCode()!=null) {
			sql.append("AND HDL_USER_ACTIVECODE=?");
			pstm.add(user.getHdlUserActiveCode());
		}
		if(user.getHdlUserBirthday()!=null) {
			sql.append("AND Hdl_User_Birthday=?");
			pstm.add(user.getHdlUserBirthday());
		}
		if(user.getHdlUserEmail()!=null) {
			sql.append("AND Hdl_User_Email ");
			pstm.add(user.getHdlUserEmail());
		}
		if(user.getHdlUserId()!=null) {
			sql.append("AND Hdl_User_Id ");
			pstm.add(user.getHdlUserId());
		}
		if(user.getHdlUserLog()!=null) {
			sql.append("AND Hdl_User_Log ");
			pstm.add(user.getHdlUserLog());
		}
		if(user.getHdlUserName()!=null) {
			sql.append("AND Hdl_User_Name ");
			pstm.add(user.getHdlUserName());
		}
		if(user.getHdlUserPhone()!=null) {
			sql.append("AND Hdl_User_Phone ");
			pstm.add(user.getHdlUserPhone());
		}
		if(user.getHdlUserPsd()!=null) {
			sql.append("AND Hdl_User_Psd ");
			pstm.add(user.getHdlUserPsd());
		}
		if(user.getHdlUserRealname()!=null) {
			sql.append("AND Hdl_User_Realname ");
			pstm.add(user.getHdlUserRealname());
		}
		if(user.getHdlUserSalt()!=null) {
			sql.append("AND Hdl_User_Salt ");
			pstm.add(user.getHdlUserSalt());
		}
		if(user.getHdlUserScore()!=null) {
			sql.append("AND Hdl_User_Score ");
			pstm.add(user.getHdlUserScore());
		}
		if(user.getHdlUserSex()!=null) {
			sql.append("AND Hdl_Use_rSex ");
			pstm.add(user.getHdlUserSex());
		}
		if(user.getHdlUserStatus()!=null) {
			sql.append("AND Hdl_User_Status ");
			pstm.add(user.getHdlUserStatus());
		}
		if(user.getHdlUserWX()!=null) {
			sql.append("AND Hdl_User_WX ");
			pstm.add(user.getHdlUserWX());
		}
		if(user.getHdlUserZFB()!=null) {
			sql.append("AND Hdl_User_ZFB ");
			pstm.add(user.getHdlUserZFB());
		}
		if(user.getSysRoleId()!=null) {
			sql.append("AND Sys_Role_Id ");
			pstm.add(user.getSysRoleId());
		}
		}

	//根据编号修改，只会修改非空字段
		@Override
		public int updateSelective(HdlUser user) throws SQLException {
			
			return DBUtil.updateSelective(user);
		}

		@Override//通过用户名删除
		public int delete(String user) throws SQLException {
			return DBUtil.execute("delete  from hdl_user where HDL_USER_ID=?", user);
		}

	
	
	
	
	
	
	
	
	
	
	
	
	
}
